<?php

      // header("Content-Type: application/json");
       
        
   
       $indicadortipo=$_GET['pointertype'];
       //$indicadortipo="departamentoi";
       $indicador=$_GET['pointer'];
       //$indicador="SJR";
       $start=$_GET['start'];
       //$start="1962";
       //$end="2014";
       $end=$_GET['end'];
       //$division="ib";
       $division=$_GET['divi'];
              
       $fd=  fopen("archivos/datosind.txt", "w");
       
       //$indicadortipo="divisioni";
       //$indicador="hola";
       
        //Se almacena la conexion en la variable $con
        //$indicador="jcr";
        $con=mysqli_connect("192.168.1.10","srojas","anemona") or die("Failed to connect with database!!!!");
        //mysqli_select_db( $con,"hermes2"); 
        mysqli_select_db($con,"hermes2");
      
        if($division==="ib" && $indicadortipo==="departamentoi" && $indicador==="JCR"){
                
            $sth=  mysqli_query($con, "select d.nombre as departamento ,avg(factor_impacto) as JCR from hermes2.papers_vs_citas as pc 
            join ifcweb_investigadores.perfiles as p on pc.autor_id=p.id join 
            ifcweb_investigadores.departamentos as d on p.id_depto=d.id join ifcweb_investigadores.divisiones as dv on d.id_division=dv.id
            where publicacion_year>=$start and publicacion_year<=$end and dv.id='1' and pc.doi is not null group by d.nombre;");
            
            $rows = array();
            $table = array();
            $table['cols'] = array(

            // Labels for your chart, these represent the column titles
            // Note that one column is in "string" format and another one is in "number" format as pie chart only required "numbers" for calculating percentage and string will be used for column title
            array('label' => 'departamento', 'type' => 'string'),
            array('label' => 'jcr', 'type' => 'number')

            );
            fwrite($fd,"División de Investigación Básica"."\r\n");
            fwrite($fd, "Departamento   JCR"."\r\n");
            while($r = mysqli_fetch_assoc($sth)) {
                fwrite($fd,$r['departamento']."\t");

                fwrite($fd, $r['JCR']."\r\n");
            $temp = array();
            // the following line will be used to slice the Pie chart
            $temp[] = array('v' => (string)  utf8_encode( $r['departamento'])); 

            // Values of each slice
            $temp[] = array('v' => (float) $r['JCR']); 
            $rows[] = array('c' => $temp);
            }

            $table['rows'] = $rows;

            //echo json_encode($table);

             fclose($fd);
        }
    
        elseif ($division==="nc" && $indicadortipo==="departamentoi" && $indicador==="JCR") {
          
        $sth = mysqli_query($con,"select d.nombre as departamento ,avg(factor_impacto) as JCR from hermes2.papers_vs_citas as pc 
                    join ifcweb_investigadores.perfiles as p on pc.autor_id=p.id join 
                    ifcweb_investigadores.departamentos as d on p.id_depto=d.id join ifcweb_investigadores.divisiones as dv on d.id_division=dv.id
                    where publicacion_year>=$start and publicacion_year<=$end and dv.id='2' and pc.doi is not null group by d.nombre;");
        $rows = array();
        $table = array();
        $table['cols'] = array(

        // Labels for your chart, these represent the column titles
        // Note that one column is in "string" format and another one is in "number" format as pie chart only required "numbers" for calculating percentage and string will be used for column title
        array('label' => 'departamento', 'type' => 'string'),
        array('label' => 'JCR', 'type' => 'number')

        );
        fwrite($fd,"División de Neurociencias"."\r\n");
        fwrite($fd, "Departamento   JCR"."\r\n");
        while($r = mysqli_fetch_assoc($sth)) {
            fwrite($fd,$r['departamento']."\t");
            
            fwrite($fd, $r['JCR']."\r\n");
        $temp = array();
        // the following line will be used to slice the Pie chart
        $temp[] = array('v' => (string)  utf8_encode( $r['departamento'])); 

        // Values of each slice
        $temp[] = array('v' => (float) $r['JCR']); 
        $rows[] = array('c' => $temp);
        }

        $table['rows'] = $rows;
        
        //echo json_encode($table);
        
        
        fclose($fd);
        }
        elseif ($division==="ib" && $indicadortipo==="investigadori" && $indicador==="JCR"){
       
        $sth = mysqli_query($con,"select p.nombre as investigador, avg(factor_impacto) as JCR from hermes2.papers_vs_citas as pc 
            join ifcweb_investigadores.perfiles as p on pc.autor_id=p.id 
            join ifcweb_investigadores.departamentos as d on p.id_depto=d.id 
            where publicacion_year>=1964 and publicacion_year<=2014 and d.id_division=1 and pc.doi is not null group by p.nombre order by jcr asc;");
        $rows = array();
        $table = array();
        $table['cols'] = array(

        // Labels for your chart, these represent the column titles
        // Note that one column is in "string" format and another one is in "number" format as pie chart only required "numbers" for calculating percentage and string will be used for column title
        array('label' => 'investigador', 'type' => 'string'),
        array('label' => 'JCR', 'type' => 'number'),
        
        
        );
        fwrite($fd, "Division de Investigación Básica"."\r\n");
        fwrite($fd, "Investigador   JCR"."\r\n");
        while($r = mysqli_fetch_assoc($sth)) {
            fwrite($fd,$r['investigador']."\t");
            
            fwrite($fd, $r['JCR']."\r\n");
        $temp = array();
        // the following line will be used to slice the Pie chart
        $temp[] = array('v' => (string)  utf8_encode( $r['investigador'])); 

        // Values of each slice
        $temp[] = array('v' => (float) $r['JCR']);
        /*$temp[]=array(
          array('v' => (float) $r['factor_impacto']),
          array('w' => (float) $r['sjr'])
        );*/
        $rows[] = array('c' => $temp);
        }

        $table['rows'] = $rows;
        
        //echo json_encode($table);
        fclose($fd);
        }
        
        elseif ($division==="nc" && $indicadortipo==="investigadori" && $indicador==="JCR"){
           
        $sth = mysqli_query($con,"select p.nombre as investigador, avg(factor_impacto) as JCR from hermes2.papers_vs_citas as pc 
            join ifcweb_investigadores.perfiles as p on pc.autor_id=p.id 
            join ifcweb_investigadores.departamentos as d on p.id_depto=d.id 
            where publicacion_year>=1964 and publicacion_year<=2014 and d.id_division=2 and pc.doi is not null group by p.nombre order by jcr asc;");
        $rows = array();
        $table = array();
        $table['cols'] = array(

        // Labels for your chart, these represent the column titles
        // Note that one column is in "string" format and another one is in "number" format as pie chart only required "numbers" for calculating percentage and string will be used for column title
        array('label' => 'investigador', 'type' => 'string'),
        array('label' => 'JCR', 'type' => 'number'),
        
        
        );
        fwrite($fd, "Division de Neurociencias"."\r\n");
        fwrite($fd, "Investigador   JCR"."\r\n");
        while($r = mysqli_fetch_assoc($sth)) {
            fwrite($fd,$r['investigador']."\t");
            
            fwrite($fd, $r['JCR']."\r\n");
        $temp = array();
        // the following line will be used to slice the Pie chart
        $temp[] = array('v' => (string)  utf8_encode( $r['investigador'])); 

        // Values of each slice
        $temp[] = array('v' => (float) $r['JCR']);
        /*$temp[]=array(
          array('v' => (float) $r['factor_impacto']),
          array('w' => (float) $r['sjr'])
        );*/
        $rows[] = array('c' => $temp);
        }

        $table['rows'] = $rows;
        
        //echo json_encode($table);
        fclose($fd);
        }
        
        elseif($division==="ib" && $indicadortipo==="departamentoi" && $indicador==="SJR"){
            
         
        $sth = mysqli_query($con,"select d.nombre as departamento,avg(sjr) as SJR from hermes2.papers_vs_citas as pc 
                join ifcweb_investigadores.perfiles as p on pc.autor_id=p.id 
                join ifcweb_investigadores.departamentos as d on p.id_depto=d.id 
                join ifcweb_investigadores.divisiones as dv on d.id_division=dv.id 
                where publicacion_year>=$start and publicacion_year<=$end and dv.id='1'and pc.doi is not null group by d.nombre;");
        
        $rows = array();
        $table = array();
        $table['cols'] = array(

        // Labels for your chart, these represent the column titles
        // Note that one column is in "string" format and another one is in "number" format as pie chart only required "numbers" for calculating percentage and string will be used for column title
        array('label' => 'departamento', 'type' => 'string'),
        array('label' => 'SJR', 'type' => 'number')
        
        
        );
        fwrite($fd, "Division de Investigación Básica"."\r\n");
        fwrite($fd, "Departamento   SJR"."\r\n");
        while($r = mysqli_fetch_assoc($sth)) {
            fwrite($fd,$r['departamento']."\t");
            
            fwrite($fd, $r['SJR']."\r\n");
        $temp = array();
        // the following line will be used to slice the Pie chart
        $temp[] = array('v' => (string)  utf8_encode( $r['departamento'])); 

        // Values of each slice
        $temp[] = array('v' => (float) $r['SJR']);
    
          
        
        
        
        $rows[] = array('c' => $temp);
        }

        $table['rows'] = $rows;
        fclose($fd);
        }
        elseif($division==="nc" && $indicadortipo==="departamentoi" && $indicador==="SJR"){
            
               
        $sth = mysqli_query($con,"select d.nombre as departamento,avg(sjr) as SJR from hermes2.papers_vs_citas as pc 
                join ifcweb_investigadores.perfiles as p on pc.autor_id=p.id 
                join ifcweb_investigadores.departamentos as d on p.id_depto=d.id 
                join ifcweb_investigadores.divisiones as dv on d.id_division=dv.id 
                where publicacion_year>=$start and publicacion_year<=$end and dv.id='2' and pc.doi is not null group by d.nombre;");
        $rows = array();
        $table = array();
        $table['cols'] = array(

        // Labels for your chart, these represent the column titles
        // Note that one column is in "string" format and another one is in "number" format as pie chart only required "numbers" for calculating percentage and string will be used for column title
        array('label' => 'departamento', 'type' => 'string'),
        array('label' => 'SJR', 'type' => 'number'),
        
        
        );
        fwrite($fd, "Division de Neurociencias"."\r\n");
        fwrite($fd, "Departamento   SJR"."\r\n");
        while($r = mysqli_fetch_assoc($sth)) {
            fwrite($fd,$r['departamento']."\t");
            
            fwrite($fd, $r['SJR']."\r\n");
        $temp = array();
        // the following line will be used to slice the Pie chart
        $temp[] = array('v' => (string)  utf8_encode( $r['departamento'])); 

        // Values of each slice
        $temp[] = array('v' => (float) $r['SJR']);
        /*$temp[]=array(
          array('v' => (float) $r['factor_impacto']),
          array('w' => (float) $r['sjr'])
        );*/
        $rows[] = array('c' => $temp);
        }

        $table['rows'] = $rows;
        fclose($fd);
        }
        elseif($division==="ib" && $indicadortipo==="investigadori" && $indicador==="SJR"){
            
        
        $sth = mysqli_query($con,"select p.nombre as investigador, avg(sjr) as SJR from hermes2.papers_vs_citas as pc 
                join ifcweb_investigadores.perfiles as p on pc.autor_id=p.id 
                join ifcweb_investigadores.departamentos as d on p.id_depto=d.id 
                where publicacion_year>=$start and publicacion_year<=$end and d.id_division='1' and pc.doi is not null group by p.nombre order by sjr asc;");
        $rows = array();
        $table = array();
        $table['cols'] = array(

        // Labels for your chart, these represent the column titles
        // Note that one column is in "string" format and another one is in "number" format as pie chart only required "numbers" for calculating percentage and string will be used for column title
        array('label' => 'investigador', 'type' => 'string'),
        array('label' => 'SJR', 'type' => 'number'),
        
        
        );
        fwrite($fd, "Division de Investigación Básica"."\r\n");
        fwrite($fd, "Investigador   SJR"."\r\n");
        while($r = mysqli_fetch_assoc($sth)) {
            fwrite($fd,$r['investigador']."\t");
            
            fwrite($fd, $r['SJR']."\r\n");
        $temp = array();
        // the following line will be used to slice the Pie chart
        $temp[] = array('v' => (string)  utf8_encode( $r['investigador'])); 

        // Values of each slice
        $temp[] = array('v' => (float) $r['SJR']);
        /*$temp[]=array(
          array('v' => (float) $r['factor_impacto']),
          array('w' => (float) $r['sjr'])
        );*/
        $rows[] = array('c' => $temp);
        }

        $table['rows'] = $rows;
        fclose($fd);
        }
        
        elseif($division==="nc" && $indicadortipo==="investigadori" && $indicador==="SJR"){
            
        
        $sth = mysqli_query($con,"select p.nombre as investigador, avg(sjr) as SJR from hermes2.papers_vs_citas as pc 
                join ifcweb_investigadores.perfiles as p on pc.autor_id=p.id 
                join ifcweb_investigadores.departamentos as d on p.id_depto=d.id 
                where publicacion_year>=$start and publicacion_year<=$end and d.id_division='2' and pc.doi is not null group by p.nombre order by sjr asc;");
        $rows = array();
        $table = array();
        $table['cols'] = array(

        // Labels for your chart, these represent the column titles
        // Note that one column is in "string" format and another one is in "number" format as pie chart only required "numbers" for calculating percentage and string will be used for column title
        array('label' => 'investigador', 'type' => 'string'),
        array('label' => 'SJR', 'type' => 'number'),
        
        
        );
        fwrite($fd, "Division de Neurociencias"."\r\n");
        fwrite($fd, "Investigador   SJR"."\r\n");
        while($r = mysqli_fetch_assoc($sth)) {
            fwrite($fd,$r['investigador']."\t");
            
            fwrite($fd, $r['SJR']."\r\n");
        $temp = array();
        // the following line will be used to slice the Pie chart
        $temp[] = array('v' => (string)  utf8_encode( $r['investigador'])); 

        // Values of each slice
        $temp[] = array('v' => (float) $r['SJR']);
        /*$temp[]=array(
          array('v' => (float) $r['factor_impacto']),
          array('w' => (float) $r['sjr'])
        );*/
        $rows[] = array('c' => $temp);
        }

        $table['rows'] = $rows;
        fclose($fd);
        }
        
        elseif($division==="ib" && $indicadortipo==="departamentoi" && $indicador==="JCRSJR"){
            
               
        $sth = mysqli_query($con,"select d.nombre as departamento ,avg(factor_impacto) as JCR, avg(sjr) as SJR from hermes2.papers_vs_citas as pc 
join ifcweb_investigadores.perfiles as p on pc.autor_id=p.id 
join ifcweb_investigadores.departamentos as d on p.id_depto=d.id 
join ifcweb_investigadores.divisiones as dv on d.id_division=dv.id where publicacion_year>=$start and publicacion_year<=$end and dv.id='1' and pc.doi is not null group by d.nombre;");
        $rows = array();
        $table = array();
        $table['cols'] = array(

        // Labels for your chart, these represent the column titles
        // Note that one column is in "string" format and another one is in "number" format as pie chart only required "numbers" for calculating percentage and string will be used for column title
        array('label' => 'departamento', 'type' => 'string'),
        array('label' => 'JCR', 'type' => 'number'),
        array('label' => 'SJR', 'type' => 'number')
        
        );
        fwrite($fd, "Division de Investigación Básica");
        fwrite($fd, "Division   JCR SJR"."\r\n");
        while($r = mysqli_fetch_assoc($sth)) {
            fwrite($fd,$r['departamento']."\t");
            
            fwrite($fd, $r['JCR']."\t");
            
            fwrite($fd, $r['SJR']."\r\n");
        $temp = array();
        // the following line will be used to slice the Pie chart
        $temp[] = array('v' => (string)  utf8_encode( $r['departamento'])); 

        // Values of each slice
        $temp[] = array('v' => (float) $r['JCR']);
        $temp[]=  array('v' => (float) $r['SJR']);
        
        $rows[] = array('c' => $temp);
        }

        $table['rows'] = $rows;
        fclose($fd);
        }
        elseif($division==="nc" && $indicadortipo==="departamentoi" && $indicador==="JCRSJR"){
            
               
        $sth = mysqli_query($con,"select d.nombre as departamento,avg(factor_impacto) as JCR, avg(sjr) as SJR from hermes2.papers_vs_citas as pc 
join ifcweb_investigadores.perfiles as p on pc.autor_id=p.id 
join ifcweb_investigadores.departamentos as d on p.id_depto=d.id
join ifcweb_investigadores.divisiones as dv on d.id_division=dv.id 
where publicacion_year>=$start and publicacion_year<=$end and dv.id='2' and pc.doi is not null group by d.nombre;");
        $rows = array();
        $table = array();
        $table['cols'] = array(

        // Labels for your chart, these represent the column titles
        // Note that one column is in "string" format and another one is in "number" format as pie chart only required "numbers" for calculating percentage and string will be used for column title
        array('label' => 'departamento', 'type' => 'string'),
        array('label' => 'JCR', 'type' => 'number'),
        array('label' => 'SJR','type' => 'number')
        
        );
        fwrite($fd, "División de Neurociencias"."\r\n");
        fwrite($fd, "Departamento   JCR SJR"."\r\n");
        while($r = mysqli_fetch_assoc($sth)) {
            fwrite($fd,$r['departamento']."\t");
            
            fwrite($fd, $r['JCR']."\t");
            
            fwrite($fd, $r['SJR']."\r\n");
        $temp = array();
        // the following line will be used to slice the Pie chart
        $temp[] = array('v' => (string)  utf8_encode( $r['departamento'])); 

        // Values of each slice
        $temp[] = array('v' => (float) $r['JCR']);
        $temp[]=  array('v' => (float) $r['SJR']);
          
        
        $rows[] = array('c' => $temp);
        }

        $table['rows'] = $rows;
        fclose($fd);
        }
        elseif($division==="ib" && $indicadortipo==="investigadori" && $indicador==="JCRSJR"){
            
               
        $sth = mysqli_query($con,"select p.nombre as investigador, avg(factor_impacto) as JCR, avg(sjr) as SJR from hermes2.papers_vs_citas as pc 
                join ifcweb_investigadores.perfiles as p on pc.autor_id=p.id 
                join ifcweb_investigadores.departamentos as d on p.id_depto=d.id
                where publicacion_year>=$start and publicacion_year<=$end and d.id_division='1' and pc.doi is not null group by  p.nombre order by SJR asc;");
        $rows = array();
        $table = array();
        $table['cols'] = array(

        // Labels for your chart, these represent the column titles
        // Note that one column is in "string" format and another one is in "number" format as pie chart only required "numbers" for calculating percentage and string will be used for column title
        array('label' => 'investigador', 'type' => 'string'),
        array('label' => 'JCR', 'type' => 'number'),
        array('label' => 'SJR','type'=>'number')
        
        );
        fwrite($fd, "División de Investigación Básica"."\r\n");
        fwrite($fd, "Investigador   JCR SJR"."\r\n");
        while($r = mysqli_fetch_assoc($sth)) {
            fwrite($fd,$r['investigador']."\t");
            
            fwrite($fd, $r['JCR']."\t");
            
            fwrite($fd, $r['SJR']."\r\n");
        $temp = array();
        // the following line will be used to slice the Pie chart
        $temp[] = array('v' => (string)  utf8_encode( $r['investigador'])); 

        // Values of each slice
        $temp[] = array('v' => (float) $r['JCR']);
        $temp[]=  array('v' => (float) $r['SJR']);
         
        
        $rows[] = array('c' => $temp);
        }
        

        $table['rows'] = $rows;
        fclose($fd);
        }
        
        elseif($division==="nc" && $indicadortipo==="investigadori" && $indicador==="JCRSJR"){
            
               
        $sth = mysqli_query($con,"select p.nombre as investigador, avg(factor_impacto) as JCR, avg(sjr) as SJR from hermes2.papers_vs_citas as pc 
                join ifcweb_investigadores.perfiles as p on pc.autor_id=p.id 
                join ifcweb_investigadores.departamentos as d on p.id_depto=d.id
                where publicacion_year>=$start and publicacion_year<=$end and d.id_division=2 and pc.doi is not null group by  p.nombre order by SJR asc;");
        $rows = array();
        $table = array();
        $table['cols'] = array(

        // Labels for your chart, these represent the column titles
        // Note that one column is in "string" format and another one is in "number" format as pie chart only required "numbers" for calculating percentage and string will be used for column title
        array('label' => 'investigador', 'type' => 'string'),
        array('label' => 'JCR', 'type' => 'number'),
        array('label' => 'SJR','type'=>'number')
        
        );
        fwrite($fd, "División de Neurociencias"."\r\n");
        fwrite($fd, "Investigador   JCR SJR"."\r\n");
        while($r = mysqli_fetch_assoc($sth)) {
            fwrite($fd,$r['investigador']."\t");
            
            fwrite($fd, $r['JCR']."\t");
            
            fwrite($fd, $r['SJR']."\r\n");
        $temp = array();
        // the following line will be used to slice the Pie chart
        $temp[] = array('v' => (string)  utf8_encode( $r['investigador'])); 

        // Values of each slice
        $temp[] = array('v' => (float) $r['JCR']);
        $temp[]=  array('v' => (float) $r['SJR']);
         
        
        $rows[] = array('c' => $temp);
        }
        

        $table['rows'] = $rows;
        fclose($fd);
        }
        
        
        
        //}
        

        mysqli_close($con);
       
        
        //$jsonTable = json_encode($table);
        echo json_encode($table);
        //echo ''.json_encode($jsonTable).'';
        //echo $jsonTable;
        //print_r($jsonTable)
        
        
        //$arr = array('a' => 1, 'b' => 2, 'c' => 3, 'd' => 4, 'e' => 5);

        //
        //echo json_encode($arr);
        
        
        ?>




